package com.ycdx.zwzc.impl;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import com.ycdx.zwzc.pojo.Request;
import com.ycdx.zwzc.utils.DBPool;

public class RequestImpl {
	static Logger logger = Logger.getLogger(RequestImpl.class.getName());
	/**
	 * 添加录入时间
	 * @param req
	 * @return
	 */
	public static Request getNewRequest(Request req){
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String time = sdf.format(System.currentTimeMillis());
		req.setTime(time);
		return req;
	}
	
	/**
	 * 需求入库
	 * @param req
	 * @return
	 */
	public static int addRequest(Request req){
		String sql = "insert into tb_request(" +
				"area," +
				"serviceType," +
				"serviceNum," +
				"masterName," +
				"masterNum," +
				"supportType," +
				"productType," +
				"symptom," +
				"note," +
				"operator," +
				"time," +
				"status) values(" +
				"?,?,?,?,?,?,?,?,?,?,?,?);";	
		Object[] params = {req.getArea()
				,req.getServiceType()
				,req.getServiceNum()
				,req.getMasterName()
				,req.getMasterNum()
				,req.getSupportType()
				,req.getProductType()
				,req.getSymptom()
				,req.getNote()
				,req.getOperator()
				,req.getTime()
				,req.getStatus()};
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		
		int flag = 0;
		try {
			flag = qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("RequestImpl.class-addRequest()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("RequestImpl.class-addRequest()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}
	
	/**
	 * 更新指定对象
	 * @param req
	 * @return
	 */
	public static int updateRequest(Request req){
		String sql = "update tb_request set " +
			"area=?," +
			"serviceType=?," +
			"serviceNum=?," +
			"masterName=?," +
			"masterNum=?," +
			"supportType=?," +
			"productType=?," +
			"symptom=?," +
			"repeatCall=?," +
			"note=? where id=?";
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {req.getArea()
				,req.getServiceType()
				,req.getServiceNum()
				,req.getMasterName()
				,req.getMasterNum()
				,req.getSupportType()
				,req.getProductType()
				,req.getSymptom()
				,req.getRepeatCall()
				,req.getNote()
				,req.getId()};
		int flag = 0;
		try {
			flag = qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("RequestImpl.class-updateRequest()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("RequestImpl.class-updateRequest()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}
	
	/**
	 * 查询所有待解决需求
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static List<Request> getAllUnresolvedRequests(){
		
		List<Request> allRequests = new ArrayList<Request>();
		String sql = "select * from tb_request where status like '%待处理%' order by id desc";
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		try {
			allRequests = (List<Request>) qr.query(conn, sql, new BeanListHandler(Request.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("RequestImpl.class-getAllUnresolvedRequests()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("RequestImpl.class-getAllUnresolvedRequests()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return allRequests;
	}
	
	
	
	/**
	 * 根据业务号码查询支撑需求
	 * @param serviceNum
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static List<Request> queryRequestByServiceNum(String serviceNum){
		
		List<Request> allRequests = new ArrayList<Request>();
		String sql = "select * from tb_request  where serviceNum=? order by id desc";
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {serviceNum};
		try {
			allRequests = (List<Request>) qr.query(conn, sql, new BeanListHandler(Request.class),params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("RequestImpl.class-queryRequestByServiceNum()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("RequestImpl.class-queryRequestByServiceNum()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}		
		
		return allRequests;
	}
	
	/**
	 * 查询所有需求
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static List<Request> getAllRequests(){
		
		List<Request> allRequests = new ArrayList<Request>();
		String sql = "select * from tb_request order by id desc limit 0,50";
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		try {
			allRequests = (List<Request>) qr.query(conn, sql, new BeanListHandler(Request.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("RequestImpl.class-getAllRequests()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("RequestImpl.class-getAllRequests()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return allRequests;
	}
	
	/**
	 * 根据ID获取指定对象
	 * @param id
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static Request getRequestById(String id){
		String sql = "select * from tb_request where id=?";
		
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {id};
		Request req = new Request();
		try {
			req = (Request) qr.query(conn, sql, new BeanHandler(Request.class),params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("RequestImpl.class-getRequestById()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("RequestImpl.class-getRequestById()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return req;
	}
	/**
	 * 根据request对象更新result及reason字段
	 * @param req
	 * @return
	 */
	public static int addResult(Request req){
		String sql = "update tb_request set status=?,reason=? ,result=? where id="+req.getId();
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {req.getStatus(),req.getReason(),req.getResult()};
		
		int flag = 0;
		try {
			flag = qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("RequestImpl.class-addResult()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("RequestImpl.class-addResult()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		//System.out.println(flag);
		return flag;
	}
	
	/**
	 * 查询所有已处理需求
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static List<Request> getAllProcessedRequests(){
		
		List<Request> allRequests = new ArrayList<Request>();
		String sql = "select * from tb_request where status like '%已处理%' order by id desc";
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		try {
			allRequests = (List<Request>) qr.query(conn, sql, new BeanListHandler(Request.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("RequestImpl.class-getAllProcessedRequests()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("RequestImpl.class-getAllProcessedRequests()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return allRequests;
	}
	
	/**
	 * 查询所有已归档需求
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static List<Request> getAllArachivedRequests(){
		
		List<Request> allRequests = new ArrayList<Request>();
		String sql = "select * from tb_request where status like '%已归档%' order by id desc limit 0,50";
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		try {
			allRequests = (List<Request>) qr.query(conn, sql, new BeanListHandler(Request.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("RequestImpl.class-getAllArachivedRequests()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("RequestImpl.class-getAllArachivedRequests()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return allRequests;
	}
	
	/**
	 * 归档：根据request对象更新staus字段
	 * @param req
	 * @return
	 */
	public static int archiveRequest(Request req){
		String sql = "update tb_request set status=? ,archivedTime=?, processTime=? where id="+req.getId();
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {req.getStatus(),req.getArchivedTime(),req.getProcessTime()};
		int flag = 0;
		try {
			flag = qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("RequestImpl.class-archiveRequest()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("RequestImpl.class-archiveRequest()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		//System.out.println(flag);
		return flag;
	}
	
	/**
	 * 删除指定id的reuqest对象
	 * @param id
	 * @return
	 */
	public static int deleteRequestById(String id){
		String sql ="delete from tb_request where id=?";
		
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {id};
		int flag = 0;
		try {
			flag = qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("RequestImpl.class-deleteRequestById()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("RequestImpl.class-deleteRequestById()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}
	
	/**
	 * 根据list导出excel
	 * @param allRequests
	 * @return
	 */
	public static InputStream getStream(List<Request> allRequests) {
	  HSSFWorkbook wb = new HSSFWorkbook();//创建一个工作间
	  HSSFSheet sheet = wb.createSheet("sheet1");//创建一个sheet
	  HSSFRow row = sheet.createRow(0);//创建一行
	  //设置列宽
	  sheet.setColumnWidth(0, 2000);
	  sheet.setColumnWidth(1, 2000);
	  sheet.setColumnWidth(2, 2500);
	  sheet.setColumnWidth(3, 4500);
	  sheet.setColumnWidth(4, 4500);
	  sheet.setColumnWidth(5, 3000);
	  sheet.setColumnWidth(6, 5000);
	  sheet.setColumnWidth(7, 5000);
	  sheet.setColumnWidth(8, 3000);
	  sheet.setColumnWidth(9, 2000);
	  sheet.setColumnWidth(10, 2500);
	  sheet.setColumnWidth(11, 2500);
	  sheet.setColumnWidth(12, 6500);
	  sheet.setColumnWidth(13, 6500);
	  sheet.setColumnWidth(14, 6500);
	  sheet.setColumnWidth(15, 6500);
	  // 设置字体 
      HSSFFont font = wb.createFont();
      font.setFontHeightInPoints(( short ) 10 ); // 字体高度 
      font.setColor(HSSFFont.COLOR_NORMAL); // 字体颜色 
      font.setFontName( "宋体 " ); // 字体 
      font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 宽度 
	  
	  //生成一个样式
      HSSFCellStyle style = wb.createCellStyle();
      
      
      //设置这些样式      
      
      style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
      style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
      style.setBorderRight(HSSFCellStyle.BORDER_THIN);
      style.setBorderTop(HSSFCellStyle.BORDER_THIN);
      style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
      
      //新建标题样式
      HSSFCellStyle titlestyle = wb.createCellStyle();
      titlestyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
      titlestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
      titlestyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
      titlestyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
      titlestyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
      titlestyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
      titlestyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
      titlestyle.setFont(font);
      
      HSSFCell cell = row.createCell(0);//创建这行的第一个元素.从0开始
	  cell.setCellValue("序号");//写入内容	  
	  cell.setCellStyle(titlestyle);
	  
	  
	  cell = row.createCell(1);//同上,不多解释了
	  cell.setCellValue("地区");
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(2);
	  cell.setCellValue("业务号码");
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(3);
	  cell.setCellValue("装维人员姓名");
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(4);
	  cell.setCellValue("装维人员号码");
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(5);
	  cell.setCellValue("受理人");
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(6);
	  cell.setCellValue("受理时间");
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(7);
	  cell.setCellValue("归档时间");
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(8);
	  cell.setCellValue("处理时长");
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(9);
	  cell.setCellValue("催修次数");
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(10);
	  cell.setCellValue("支撑类别");
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(11);
	  cell.setCellValue("产品类别");
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(12);
	  cell.setCellValue("故障现象");
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(13);
	  cell.setCellValue("备注信息");
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(14);
	  cell.setCellValue("故障原因");
	  cell.setCellStyle(titlestyle);
	  
	  cell = row.createCell(15);
	  cell.setCellValue("处理结果");
	  cell.setCellStyle(titlestyle);
	  
	  //循环,将下面几行的数据取出来放入这个sheet中
	  for(int i = 0 ; i < allRequests.size() ; i++)
	  {
	   Request request = allRequests.get(i);
	   row = sheet.createRow(i+1);
	   
	   cell = row.createCell(0);
	   cell.setCellValue(request.getId());
	   cell.setCellStyle(style);
	   
	   cell = row.createCell(1);
	   cell.setCellValue(request.getArea());
	   cell.setCellStyle(style);
	   
	   cell = row.createCell(2);
	   cell.setCellValue(request.getServiceNum());
	   cell.setCellStyle(style);
	   
	   cell = row.createCell(3);
	  cell.setCellValue(request.getMasterName());
	  cell.setCellStyle(style);
	  
	  cell = row.createCell(4);
	  cell.setCellValue(request.getMasterNum());
	  cell.setCellStyle(style);
	  
	  cell = row.createCell(5);
	  cell.setCellValue(request.getOperator());
	  cell.setCellStyle(style);
	  
	  cell = row.createCell(6);
	  cell.setCellValue(request.getTime());
	  cell.setCellStyle(style);
	  
	  cell = row.createCell(7);
	  cell.setCellValue(request.getArchivedTime());
	  cell.setCellStyle(style);
	  
	  cell = row.createCell(8);
	  cell.setCellValue(request.getProcessTime());
	  cell.setCellStyle(style);
	  
	  cell = row.createCell(9);
	  cell.setCellValue(request.getRepeatCall());
	  cell.setCellStyle(style);
	  
	  cell = row.createCell(10);
	  cell.setCellValue(request.getSupportType());
	  cell.setCellStyle(style);
	  
	  cell = row.createCell(11);
	  cell.setCellValue(request.getServiceType());
	  cell.setCellStyle(style);
	  
	  cell = row.createCell(12);
	  cell.setCellValue(request.getSymptom());
	  cell.setCellStyle(style);
	  
	  cell = row.createCell(13);
	  cell.setCellValue(request.getNote());
	  cell.setCellStyle(style);
	  
	  cell = row.createCell(14);
	  cell.setCellValue(request.getReason());
	  cell.setCellStyle(style);
	  
	  cell = row.createCell(15);
	  cell.setCellValue(request.getResult());
	  cell.setCellStyle(style);
	   
	  }
	  
	  ByteArrayOutputStream os=new ByteArrayOutputStream();  
      try {  
          wb.write(os);  
            
      } catch (IOException e) {  
          e.printStackTrace();  
      }  
      byte[] bytes=os.toByteArray();  
      InputStream is=new ByteArrayInputStream(bytes);  
      return is;  
	 }
	
	@SuppressWarnings("unchecked")
	public static List<Integer> checkStatusByServiceNum(String serviceNum){
		List<Request> allRequests = new ArrayList<Request>();
		String sql = "select * from tb_request where serviceNum =? and status not like '%已归档%'";
		
		List<Integer> result = new ArrayList<Integer>();
		Object[] params = {serviceNum};
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		try {
			allRequests = (List<Request>) qr.query(conn, sql, new BeanListHandler(Request.class),params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("RequestImpl.class-getAllArachivedRequests()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("RequestImpl.class-getAllArachivedRequests()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		result.add(allRequests.size());
		return result;
	}
}
